package ar.edu.itba.it.paw.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import ar.edu.itba.it.paw.dao.CategoryDAO;
import ar.edu.itba.it.paw.domain.Category;
import ar.edu.itba.it.paw.domain.ConnectionManager;
import ar.edu.itba.it.paw.domain.exceptions.DatabaseException;


public class DatabaseCategoryDAO implements CategoryDAO {
	
	private static CategoryDAO instance;
	
	public static synchronized CategoryDAO getInstance(){
		if(instance == null)
			instance = new DatabaseCategoryDAO();
		return instance;
	}	
	
	public ArrayList<Category> getCategories(){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stm;
		ArrayList<Category> categories = new ArrayList<Category>();
		try {
			stm = conn.prepareStatement("SELECT * FROM \"Categoria\" "); 

			ResultSet results = stm.executeQuery();
			while (results.next()) {
				Category categ = new Category(results.getString("name"));
				categ.setId(results.getInt("categoryId"));
				categories.add(categ);
			}
		} catch (SQLException e) {
			throw new DatabaseException();
		}
		manager.closeConnection();
		
		return categories;
	}
	
	public Category getCategory(String name){
		ConnectionManager manager = ConnectionManager.getInstance();
		Connection conn = manager.getConnection();
		PreparedStatement stmt;
		Category categ = null;
		try{
			stmt = conn.prepareStatement("Select * from \"Categoria\" where \"name\" = ?");
			stmt.setString(1, name);
			ResultSet myCursor = stmt.executeQuery();
			if(myCursor.next()){
				categ = new Category(myCursor.getString("name"));
				categ.setId(myCursor.getInt("categoryId"));
			}
		}catch(SQLException e){
			throw new DatabaseException();
		}
		manager.closeConnection();
		return categ;
	}
}
